package hyit.app.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import hyit.app.dao.ISecondaryAccountDAO;
import hyit.app.model.SecondaryAccount;

public class SecondaryAccountDAOImpl implements ISecondaryAccountDAO {
	private Connection conn = null;
	private PreparedStatement pstmt = null;

	public SecondaryAccountDAOImpl(Connection conn) {
		this.conn = conn;
	}

	@Override
	public boolean doCreate(SecondaryAccount sa) throws Exception {
		boolean flag = false;
		String sql = "INSERT INTO secondary_account VALUES(NULL,?,?,?,?,null)";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, sa.getKeyID());
		this.pstmt.setString(2, sa.getName());
		this.pstmt.setString(3, sa.getAccount());
		this.pstmt.setString(4, sa.getPassword());
		//this.pstmt.setString(5, sa.getCardMac());
		if (this.pstmt.executeUpdate() > 0) {
			flag = true;
		}
		this.pstmt.close();
		return flag;
	}

	@Override
	public boolean update(SecondaryAccount sa) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}

	@Override
	public SecondaryAccount delete(SecondaryAccount sa) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public SecondaryAccount checkAccount(String account) throws Exception {
		SecondaryAccount sa = null;
		String sql = "SELECT id,key_id,`name`,account,`password`,card_mac FROM secondary_account WHERE account = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, account);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			sa = new SecondaryAccount();
			sa.setId(rs.getInt(1));
			sa.setKeyID(rs.getInt(2));
			sa.setName(rs.getString(3));
			sa.setAccount(rs.getString(4));
			sa.setPassword(rs.getString(5));
			sa.setCardMac(rs.getString(6));
		}
		this.pstmt.close();
		return sa;
	}

	@Override
	public SecondaryAccount validate(String account, String password)
			throws Exception {
		SecondaryAccount sa = null;
		String sql = "SELECT id,key_id,`name`,account,`password`,card_mac FROM secondary_account "
				+ "WHERE account = ? AND `password` = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, account);
		this.pstmt.setString(2, password);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			sa = new SecondaryAccount();
			sa.setId(rs.getInt(1));
			sa.setKeyID(rs.getInt(2));
			sa.setName(rs.getString(3));
			sa.setAccount(rs.getString(4));
			sa.setPassword(rs.getString(5));
			sa.setCardMac(rs.getString(6));
		}
		this.pstmt.close();
		return sa;
	}

	@Override
	public List<SecondaryAccount> getByKeyID(Integer keyID) throws Exception {
		List<SecondaryAccount> all = new ArrayList<SecondaryAccount>();
		SecondaryAccount sa = null;
		String sql = "SELECT id,key_id,`name`,account,`password`,card_mac FROM secondary_account WHERE key_id = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setInt(1, keyID);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			sa = new SecondaryAccount();
			sa.setId(rs.getInt(1));
			sa.setKeyID(rs.getInt(2));
			sa.setName(rs.getString(3));
			sa.setAccount(rs.getString(4));
			sa.setPassword(rs.getString(5));
			sa.setCardMac(rs.getString(6));
			all.add(sa);
		}
		this.pstmt.close();
		return all;
	}

	@Override
	public SecondaryAccount validateByCard(String cardMac) throws Exception {
		SecondaryAccount sa = null;
		String sql = "SELECT id,key_id,`name`,account,`password`,card_mac FROM secondary_account "
				+ "WHERE card_mac = ?";
		this.pstmt = this.conn.prepareStatement(sql);
		this.pstmt.setString(1, cardMac);
		ResultSet rs = this.pstmt.executeQuery();
		while (rs.next()) {
			sa = new SecondaryAccount();
			sa.setId(rs.getInt(1));
			sa.setKeyID(rs.getInt(2));
			sa.setName(rs.getString(3));
			sa.setAccount(rs.getString(4));
			sa.setPassword(rs.getString(5));
			sa.setCardMac(rs.getString(6));
		}
		this.pstmt.close();
		return sa;
	}

}
